Mega Code Archive
Text Functions
1) =CLEAN(CHAR(7)&text&CHAR(7))
2) =CODE(java2s)
3) =DOLLAR(A2, -2)
4) =DOLLAR(A5)
5) =EXACT(zero ,zero) returns FALSE because the first string contains a trailing space
6) =FIND(m,A2)
7) =FIND(M,A2,3)
8) =FIXED(-1234 567, -1, TRUE)
9) =FIXED(44 332)
10) =FIXED(A2, -1)
11) =IF(LEFT(A1,1)= ,SUBSTITUTE(A1, ,,1), A1)
12) =LEFT(A3)
13) =LENB(ABC)
14) =MIDB(ABCasdf,4,2)
15) =REPLACE(A3,3,2,10)
16) =RIGHT(A3)
17) =SEARCH (bd,A1)
18) =SEARCH (Birthday, A1)
19) =SEARCH (y,A1) returns the first place where a y is followed by any two characters
20) =SEARCH (yo,A1) returns the first place where yo is followed by any single character
21) =SUBSTITUTE(a b c d, ,,,1)
22) =SUBSTITUTE(a b c d, ,,,3)
23) =SUBSTITUTE(a b c f, ,,)
24) =SUBSTITUTE(A3, 1, 2, 1)
25) =SUBSTITUTE(two two ,two, three)
26) =SUBSTITUTE(two two ,two, three,2)
27) =TEXT(0 4,#%)
28) =TEXT(1234 56,# #) (1)
29) =TEXT(1234 56,$#) (3)
30) =TEXT(3152005,mmddyy)
31) =TEXT(3152005,mmddyyyy)
32) =TEXT(3152005,mmm-dd)
33) A case-insensitive comparison
34) ASC changes full-width (double-byte) English letters to half-width (single-byte) characters
35) BAHTTEXT(number) converts a number to text, using the baht currency format
36) CHAR(number) returns the character specified by the code number
37) CLEAN removes all nonprintable characters from text
38) CODE(text) returns a numeric code for the first character in a text string
39) CONCATENATE (text1,text2, ) joins several text items into one text item
40) Count the number of Bs (both upperand lowercase) in the string
41) Count the number of Bs (uppercase only) in the string
42) Display asterisk padding on both sides of the number
43) Display text such as Today is Friday, September 14, 2007
44) Display the value along with the asterisk padding on the left
45) Displaying Formatted Currency Values as Text
46) DOLLAR converts a number to text, using the $ (dollar) currency format
47) EXACT(text1,text2) checks to see if two text values are identical
48) Extracting the First Word of a String
49) Extracting the First Word of a String with ISERR
50) FIND(find_text,within_text,start_num) finds one text value within another (case-sensitive) and return the index
51) FIXED(number,decimals,no_commas) formats a number as text with a fixed number of decimals
52) Get count of characters where the space is from left
53) If cell A1 contains the letter A (uppercase) The following formula returns the letter a (lowercase)
54) If the argument for CODE consists of more than one character, the function uses only the first character
55) LEFT(text,num_chars) returns the leftmost characters from a text value
56) LEN(text), LENB(text) returns the number of characters in a text
57) LOWER(text) converts text to lowercase
58) Pad the number with asterisks on the left
59) Pad the number with dashes
60) PHONETIC(reference) extracts the phonetic (furigana) characters from a text string
61) PROPER(text) capitalizes the first letter in each word of a text value
62) REPLACEB(old_text,start_num,num_bytes,new_text) replaces characters within text
63) REPT(text,number_times) repeats text a given number of times
64) Return the number of occurrences of a substring in B1 within a string in A1
65) Returns the first nine characters plus an ellipsis for longer string
66) Returns the number of words
67) RIGHT(text,num_chars), RIGHTB(text,num_bytes) returns the rightmost characters from a text value
68) Search for the word Report and replaces those six characters it with the word Summary
69) Sentence case
70) Strips space, hyphen, colon, asterisk, underscore, left parenthesis, and right parenthesis
71) SUBSTITUTE(text,old_text,new_text,instance_num) substitutes new text for old text in a text string
72) TEXT(A1,$#,##0 00)
73) TEXT(AVERAGE(A
74) TEXT(value,format_text) formats a number and converts it to text
75) To demonstrate the opposing nature of the CODE and CHAR functions
76) To pad the number with asterisks, use two asterisks
77) TRIM(text) removes spaces from text
78) UPPER(text) converts text to uppercase
79) Use the REPLACE function to replace one character beginning at position 5 with nothing
80) Use the SUBSTITUTE function to remove all spaces from a string
81) Uses the NOW and TEXT function to format the date and time
82) Using the CHAR function to represent a comma (44) and a space (32) between the two entries
83) VALUE(text) converts a text argument to a number